﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;
using System.Text.RegularExpressions;
using System.Xml.Serialization;
using System.IO;
using System.Windows.Forms;

namespace ExcelPlugin {
    public class OperationUtil {
        #region 外部API 
        private static Excel.Range selCellRec;
        private static Excel.Range referCellRec;
        public static void GotoSubSheet() {
            try {
                Excel.Range sel = ExcelGetSingleSelection();
                if(sel == null) return;
                string tableName = ExcelGetHeadNameOfCell(sel);
                if(string.IsNullOrEmpty(tableName)) {
                    MsgHelper.PopErrorMsg("获取表头名失败");
                    return;
                }

                Excel.Worksheet subSheet = ExcelGetSubSheetByTableName(tableName);
                if(subSheet == null) {
                    return;
                }

                Excel.Range columnData = subSheet.Columns[1];
                Excel.Range target = columnData.Find(sel.Value2.ToString());
                if(target == null) {
                    return;
                }
                selCellRec = sel;
                subSheet.Activate();
                target.Select();
            }
            catch(Exception e) {
                MsgHelper.PopErrorMsg(e.ToString());
                throw;
            }
        }
        public static void BackReferCell() {
            if(selCellRec == null) {
                return;
            }
            referCellRec = ExcelGetSingleSelection();

            selCellRec.Worksheet.Activate();
            selCellRec.Select();
        }
        public static void ForwardReferCell() {
            if(referCellRec == null) {
                return;
            }
            referCellRec.Worksheet.Activate();
            referCellRec.Select();
        }
        public static void ChangeTableHead() {
            var dlg = new OpenFileDialog();
            dlg.Filter = "Excel文件 (*.xlsx)|*.xlsx";
            dlg.Multiselect = true;
            dlg.Title = "请选择需要修改的文件";
            if(dlg.ShowDialog() != DialogResult.OK) return;

            string[] name_value = new string[3];
            string[] text = new string[] { "Sheet名称", "旧名称", "新名称"};
            DialogResult inputRes = MsgHelper.ShowInputBoxForm("输入Sheet名称，旧表头名，新表头名", "", 3, text, ref name_value);
            if(inputRes != DialogResult.OK) return;

            MsgHelper.PopQueryMsg("确认修改吗？", ()=> { ExcelBatchChangeTableHead(dlg.FileNames, name_value); });
        }
        public static void FindIdDefine() {
            try {
                Excel.Range sel = ExcelGetSingleSelection();
                if(sel == null) return;

                string val = sel.Value2.ToString();
                if(string.IsNullOrEmpty(val)) return;

                //找到当前单元格的表头, record记录中找
                string headName = ExcelGetHeadNameOfCell(sel);
                if(string.IsNullOrEmpty(headName)) return;

                Excel.Range cell = XmlLoader.GetDefineCell(headName, val);
                if(cell == null) {
                    MsgHelper.PopInformationMsg("找不到引用定义");
                    return;
                }
                selCellRec = sel;
                cell.Worksheet.Activate();
                cell.Activate();
            }
            catch(Exception e) {
                MsgHelper.PopErrorMsg(e.ToString()); 
            }
        }
        public static void SetEnumColumnValidation(Excel.Workbook wb) {
            for(int sheet_it = 1; sheet_it <= wb.Worksheets.Count; sheet_it++) {
                Excel.Worksheet sheet = wb.Worksheets[sheet_it];
                for(int column_it = 1; column_it <= sheet.UsedRange.Columns.Count; column_it++) {
                    Excel.Range column = sheet.UsedRange.Columns[column_it];
                    ExcelSetEnumFilter(column, ExcelGetRelativeName(wb.FullName), sheet, sheet_it == 1);
                }
                sheet.CircleInvalid();
            }
        }
        public static void OnWorkbookActivate(Excel.Workbook wb) {
            try {
                GlobalBlackBoard.cur_wb_relative_path = ExcelGetRelativeName(wb.FullName);
                GlobalBlackBoard.AddWBFullName(wb.FullName, wb);
                XmlLoader.LoadXmlConfigs();
                XmlLoader.SetupCurrenExcelRecord(); //新的workbook激活时，建立Excel和Xml间的反向索引
                SetEnumColumnValidation(wb); //根据Xml中的枚举定义，设置WorkBook对应Column的有效性校验
            }
            catch(Exception e) {
                MsgHelper.PopErrorMsg(e.ToString());
                throw;
            }
        }
        public static void AddInInit() {
            var app = ExcelDnaUtil.Application as Excel.Application;
            app.WorkbookActivate += OnWorkbookActivate;             
            app.WorkbookBeforeClose += (Excel.Workbook wb, ref bool cancel) =>
            {
                GlobalBlackBoard.RemoveWBFullName(wb.FullName);
            };
        }
        public static void ShowDefineFileNames() {
            string names = XmlLoader.GetDefineFileNames();
            if(string.IsNullOrEmpty(names)) {
                MsgHelper.PopInformationMsg("未找到当前Excel的数据定义文件！");
                return;
            }
            MsgHelper.PopInformationMsg(names);
        }

        private static string _guessDataType(Excel.Range valueCell) {
            try {
                string validStr = valueCell.Validation.Formula1.ToString();

                return "enum";
            }
            catch(Exception) {
            }

            try {
                int guessInt;
                if(int.TryParse(valueCell.Value2.ToString(), out guessInt)) return "int";
            }
            catch(Exception) {
            }

            return "string";
        }

        private static void TryAddEnum(XmlMetalib mt, XmlFieldEntry refer_entry, Excel.Range cell) {
            try {
                string[] enumVal = cell.Validation.Formula1.ToString().Split(',');
                XmlEnumSet es = new XmlEnumSet();

                es.name = string.Format("{0}_enum", refer_entry.key);
                for(int i = 0; i < enumVal.Length; i++) {
                    XmlEnumEntry ee = new XmlEnumEntry();
                    ee.name = enumVal[i];
                    ee.key = enumVal[i];
                    ee.value = i;

                    if(es.EntryList == null) es.EntryList = new List<XmlEnumEntry>();
                    es.EntryList.Add(ee);
                }

                refer_entry.type_param = es.name;
                mt.enum_config.Add(es);
            }
            catch(Exception) {
            }
        }

        private static void xmlGenerateOneSheet(Excel.Worksheet ws, int startIdx, XmlBaseRefer result, XmlMetalib mt) {
            for(int i = startIdx; i <= ws.UsedRange.Columns.Count; i++) {
                Excel.Range column = ws.Columns[i];
                Excel.Range cell = column.Rows[1];
                string headName = cell.Value2.ToString();

                XmlFieldEntry fe = new XmlFieldEntry();
                fe.name = headName;
                fe.key = headName;
                fe.type = _guessDataType(column.Rows[2]);  //取数据第一行，猜一下类型
                if(fe.type == "enum") TryAddEnum(mt, fe, column.Rows[2]);

                if(result.fieldCfg == null) result.fieldCfg = new List<XmlFieldEntry>();
                result.fieldCfg.Add(fe);
            }
        }
        public static void GenerateXmlTemplate() {
            try {

                Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
                var wb = app.ActiveWorkbook;
                if(string.IsNullOrEmpty(wb.Path)) {
                    MsgHelper.PopInformationMsg("请打开一份Excel数据!");
                    return;
                }
                XmlMetalib mt = new XmlMetalib();
                mt.lib_name = wb.Name;
                Excel.Worksheet main_ws = wb.Worksheets[1];

                XmlClassSet cs = new XmlClassSet();
                if(mt.class_config == null) mt.class_config = new List<XmlClassSet>();
                mt.class_config.Add(cs);
                cs.name = main_ws.Name;
                cs.desc = main_ws.Name;
                xmlGenerateOneSheet(main_ws, 1, cs, mt); //主Sheet生成

                if(mt.group_config == null) mt.group_config = new List<XmlGroupSet>(); 
                for(int i = 2; i <= wb.Worksheets.Count; i++) {
                    XmlGroupSet gs = new XmlGroupSet();
                    Excel.Worksheet subSheet = wb.Worksheets[i];
                    xmlGenerateOneSheet(subSheet, 2, gs, mt);
                    mt.group_config.Add(gs);
                }

                string resultDir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "_RES_XMLTEMP");
                if(!Directory.Exists(resultDir)) {
                    Directory.CreateDirectory(resultDir);
                }
                string resultPath = string.Format("{0}.xml", Path.Combine(resultDir, main_ws.Name));

                Action serXml = () =>
                {
                    FileStream fs = new FileStream(resultPath, FileMode.OpenOrCreate, FileAccess.Write);
                    XmlSerializer serializer = new XmlSerializer(typeof(XmlMetalib));
                    XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
                    ns.Add(string.Empty, string.Empty);
                    serializer.Serialize(fs, mt);
                    fs.Flush();
                    fs.Close();
                };
                if(File.Exists(resultPath)) {
                    MsgHelper.PopQueryMsg(string.Format("文件：{0}已经存在，确认要覆盖吗？", resultPath), serXml);
                }
                else {
                    serXml();
                }
            }
            catch(Exception e) {
                MsgHelper.PopErrorMsg(e.ToString());
            }
        }
        #endregion

        #region Excel操作
        static string ExcelGetRelativeName(string fullName) {
            string source_path_match = Path.Combine(WS_PATH.WS_PATH_PREFIX, WS_PATH.WS_PATH_SOURCE);
            int index = fullName.LastIndexOf(source_path_match);
            if(index < 0) {
                return string.Empty;
            }

            return fullName.Substring(index + source_path_match.Length);
        }
        static string[] ExcelSplitHeadName(string headName) {
            //尝试切分表头名，替换掉每个组成部分最后一位的数字
            string[] name_parts = headName.Split('_');
            string[] new_parts = new string[name_parts.Length];
            for(int i = 0; i < name_parts.Length; i++) {
                new_parts[i] = Regex.Replace(name_parts[i], @"\d+$", "");
            }

            return new_parts;
        }
        static Excel.Worksheet ExcelGetSubSheetByTableName(string name) {
            //表头名换取子表名, 用于子表跳转
            //先尝试直接去去查找子表
            Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
            Dictionary<string, Excel.Worksheet> m_sheetMap = new Dictionary<string, Excel.Worksheet>();
            foreach(Excel.Worksheet sheet in app.ActiveWorkbook.Sheets) {
                if(sheet.Name == name) return null;
                m_sheetMap.Add(sheet.Name, sheet);
            }
            string subname = string.Join(".", ExcelSplitHeadName(name));
            Excel.Worksheet subSheet;
            if(!m_sheetMap.TryGetValue(subname, out subSheet)) {
                MsgHelper.PopErrorMsg("获取子表失败，表头名：{0}, 子表名：{1}", name, subname);
                return null;
            }

            return subSheet;
        }
        static Excel.Range ExcelGetSingleSelection() {
            Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
            Excel.Range selections = app.Selection as Excel.Range;
            if(selections.Count != 1 || selections.Value2 == null) {
                MsgHelper.PopErrorMsg("请选择一个非空的单元格进行操作");
                return null;
            }
            return selections;
        }
        static string ExcelGetCellValue(int row_id, int column_id) {
            //索引均从1开始计数
            Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
            Excel.Worksheet sheet = app.ActiveSheet;
            Excel.Range cell = sheet.Cells[row_id, column_id];
            return cell.Value2 == null ? string.Empty :cell.Value2.ToString();
        }
        static string ExcelGetHeadNameOfCell(Excel.Range cell) {
            string val = cell.Value2.ToString();
            int group_index;
            if(string.IsNullOrEmpty(val) || !int.TryParse(val, out group_index)) {
                MsgHelper.PopErrorMsg("解析子表ID索引失败, Value: 【{0}】", val);
                return string.Empty;
            }
            return ExcelGetCellValue(1, cell.Column);
        }
        static void ExcelSetEnumFilter(Excel.Range columnData, string relativeName, Excel.Worksheet sheet, bool main_sheet) {
            //main_sheet: 当前是否为是否是主表
            columnData.Validation.Delete();
            if(columnData.Rows[1].Value2 == null) {
                return;
            }
            string headName = columnData.Rows[1].Value2.ToString();
            List<string> enumKeyList = XmlLoader.GetEnumList(headName, ExcelSplitHeadName(headName), relativeName, sheet, main_sheet);
            if(enumKeyList.Count <= 0) {
                return;
            }
            Excel.Range columnNoHead = columnData.Rows[string.Format("2:{0}", columnData.Rows.Count), Type.Missing];
            columnNoHead.Validation.Add(
                Excel.XlDVType.xlValidateList,
                Excel.XlDVAlertStyle.xlValidAlertInformation,
                Excel.XlFormatConditionOperator.xlBetween,
                string.Join(",", enumKeyList.ToArray()), Type.Missing);

            columnNoHead.Validation.IgnoreBlank = false;
            columnNoHead.Validation.InCellDropdown = true;
        }
        static void ExcelBatchChangeTableHead(string[] fileNames, string[] value) {
            StringBuilder sb = new StringBuilder();
            var app = ExcelDnaUtil.Application as Excel.Application;
            app.Workbooks.Close();
            for(int i = 0; i < fileNames.Length; i++) {
                string path = fileNames[i];
                var wb = app.Workbooks.Open(path);
                wb.Activate();

                foreach(Excel.Worksheet ws in wb.Worksheets) {
                    if(ws.Name != value[0]) {
                        continue;
                    }
                    Excel.Range row = ws.UsedRange.Rows[1];
                    for(int column_it = 1; column_it <= row.Columns.Count; column_it++) {
                        Excel.Range cell = row.Columns[column_it];
                        if(cell.Value2.ToString() == value[1]) {
                            cell.Value2 = value[2];
                            sb.AppendLine(path);
                        }
                    }
                }
                app.ActiveWorkbook.Save();
                app.ActiveWorkbook.Close();
            }
            if(string.IsNullOrEmpty(sb.ToString())) {
                MsgHelper.PopInformationMsg(string.Format("成功修改以下文件：\n{0}", sb.ToString()));
            }
        }
        #endregion


    }
}
